********************************************************************************************************************************
***   Replication file for:                                                                                                  ***
***   Berbee, P., Braun, S. T. and Franke, R. (2024). Reversing Fortunes of German Regions, 1926-2019. JOEG				     ***
***   							                                                                                             ***
***   SCRIPT: 	_x0_regional_classifications.do																				 ***	
***   PURPOSE: 	Reads and prepares the classification of regional labour markets, assigning them to districts and federal 	 ***
***				states																									 	 ***
********************************************************************************************************************************

* Preamble (unnecessary when executing run.do)
run "$reversing/scripts/programs/_config.do"

************
* Code begins
************

*** Import keys for districts and federal states (Regierungsbezirke und Bundesländer)
import excel "$reversing/data/labor_markets_regional_classifications.xlsx", clear firstrow
keep Sheet1__La REGBEZ_KEN REGBEZ_NAM bula_id bula_name LLM_Name LLM_Center
rename Sheet1__La labor_market_id
rename REGBEZ_KEN rb_id
rename REGBEZ_NAM rb_name
rename LLM_Name labor_market_name
rename LLM_Center labor_market_center
rename bula_id state_id
rename bula_name state_name


** Perform corrections (where labor market cross state borders)

// assign labour market region Wiesbaden-Mainz to Rheinhessen & Rheinland-Pfalz
replace rb_id=74 if labor_market_id==98
replace rb_name="Rheinhessen" if labor_market_id==98
replace state_id=7 if labor_market_id==98
replace state_name="Rheinland-Pfalz" if labor_market_id==98

// assign labour market region Ludwigshafen-Mannheim to Nordbaden & Baden-Württemberg
replace rb_id=82 if labor_market_id==105
replace rb_name="Nordbaden" if labor_market_id==105
replace state_id=8 if labor_market_id==105
replace state_name="Baden-Württemberg" if labor_market_id==105

label var labor_market_id "Labor market ID"
label var labor_market_name "Labor market name"
label var labor_market_center "Main town/city"
label var rb_id "Regierungsbezirk ID"
label var rb_name "Regierungsbezirk name"
label var state_id "Bundesland ID"
label var state_name "Bundesland name"

sort labor_market_id

save "$reversing/processed/intermediate/labor_market_ids.dta", replace 

*** EOF

